﻿using LowCoding.Common;
using LowCoding.Common.Security;
using LowCoding.Domain;
using LowCoding.Dto;
using Mapster;
using SqlSugar;
using System.Linq.Expressions;
using System.Text;

namespace LowCoding.Application
{
    public class ServiceBase<TEntity, TEntityInput, TEntityOutput, TEntitySearch, TKey> 
        where TEntity : class, new()
        where TEntityInput : class, new()
        where TEntityOutput : class, new()
        where TEntitySearch : class, new()
        //where TEntityDetailDto : class, new()
    {
        //优点：ISqlSugarClient性能比SqlSugarScope有5%的提升
        //缺点：1、不能使用单例
        //      2、db不能跨上下文使用，每次需要new一个新对象
        //      3、偶发错误难排查，适合有经验的用户
        public readonly ISqlSugarClient _client;//SqlSugar框架的接口，它提供了一系列的方法来操作数据库

        private static string ConnectionString = ConfigHelper.Configuration["ConnectionStrings:TestDB"].ToString();//ConfigHelper工具类
        public static string _character = ASCIITool.ASCToChr().ToString();

        public ServiceBase()
        {
            _client = new SqlSugarClient(new ConnectionConfig()
            {
                DbType = DbType.MySql,//DbType枚举（支持多种数据库）
                ConnectionString = ConnectionString,//连接字符串,从底层配置文件获取
                //InitKeyType.SystemTable自动从数据库读取主键自增列的信息（适合SA等高权限的账户）,如果是这种模式实体类就普通的实体类便可以，不需要加任何东西
                //InitKeyType.Attribute 从实体类的属性中读取 主键和自增列的信息(适合有独立的运维组的用户没有系统表操作权限)如果是这种模式实体类需要有所改变
                InitKeyType = InitKeyType.Attribute,
                // 设为true相同线程是同一个SqlConnection
                //IsShardSameThread = true, // 设为true相同线程是同一个SqlConnection
                IsAutoCloseConnection = true,
                AopEvents = new AopEvents
                {
                    OnLogExecuting = (sql, p) =>
                    {
                        //记录所有执行sql语句
                        LogHelper.WriteSqlLog(sql + "\r\n" + (p == null ? "" : string.Join(",", p.Select(it => it.ParameterName + ":" + it.Value))));
                    }
                }
            });
        }

        #region ExtendMethods

        public static List<SqlFuncExternal> ExtendMethods()
        {
            return new List<SqlFuncExternal>() {
                new SqlFuncExternal()
                {
                    UniqueMethodName = "GetDomainName",
                    MethodValue = (expInfo, dbType, expContext) =>
                    {
                        if (dbType == DbType.Oracle)
                            return string.Format("COMM_PAK.GET_DOMAIN_NAME_F({0},{1})", expInfo.Args[0].MemberName,expInfo.Args[1].MemberName);
                        else
                            throw new Exception("未实现");
                    }
                }
            };
        }

        public virtual string GetDomainName(object str1, object str2)
        {
            //这里不能写任何实现代码，需要在上面的配置中实现
            throw new NotSupportedException("Can only be used in expressions");
        }

        #endregion ExtendMethods

        #region 根据条件查询记录

        /// <summary>
        /// 根据条件查询记录
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public List<TEntityOutput> GetList(TEntitySearch search)
        {
            Type et = typeof(TEntity);
            //组装条件
            var conModels = GetConditionals(search).Union(GetDefaultCondition(et)).ToList();
            var key = GetPrimaryKeys(et)[1];
            var list = _client.Queryable<TEntity>().OrderBy($"{key} desc").Where(conModels).ToList();
            return list.Adapt<List<TEntityOutput>>();
        }

        /// <summary>
        /// 根据条件查询记录
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public async Task<List<TEntityOutput>> GetListAsync(TEntitySearch search)
        {
            Type et = typeof(TEntity);
            //组装条件
            var conModels = GetConditionals(search).Union(GetDefaultCondition(et)).ToList();
            var key = GetPrimaryKeys(et)[1];
            var list = await _client.Queryable<TEntity>().OrderBy($"{key} desc").Where(conModels).ToListAsync();
            return list.Adapt<List<TEntityOutput>>();
        }

        #endregion 根据条件查询记录

        #region 获取分页列表

        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public virtual PageResponse<TEntityOutput> GetPageList(TEntitySearch search)
        {
            // 组装查询条件
            Type et = typeof(TEntity);
            //组装条件
            var conModels = GetConditionals(search).Union(GetDefaultCondition(et)).ToList();
            // 判断是否有 pageSize 参数 或者 pageSize = 0，如果没有或者等于0则查询所有
            int pageSize = 0;
            int pageNo = 0;
            if (search.IsHasProperty("page"))
                pageNo = search.GetPropertyValue<TEntitySearch, int>("page");
            if (search.IsHasProperty("limit"))
                pageSize = search.GetPropertyValue<TEntitySearch, int>("limit");
            pageSize = pageSize <= 0 ? 20 : pageSize;
            pageNo = pageNo <= 0 ? 1 : pageNo;

            // 根据主键倒序排列
            var key = GetPrimaryKeys(et)[1];
            if (pageSize == 0)
            {
                var list = _client.Queryable<TEntity>().OrderBy($"{key} desc").Where(conModels).ToList();
                List<TEntityOutput> dtos = list.Adapt<List<TEntityOutput>>();
                return new PageResponse<TEntityOutput>()
                {
                    rows = dtos,
                    total = list.Count
                };
            }
            else
            {
                int total = 0;
                var list = _client.Queryable<TEntity>().OrderBy($"{key} desc").Where(conModels).ToPageList(pageNo, pageSize, ref total);
                List<TEntityOutput> dtos = list.Adapt<List<TEntityOutput>>();
                int totalPages = (total % pageSize == 0) ? total / pageSize : total / pageSize + 1;
                return new PageResponse<TEntityOutput>()
                {
                    rows = dtos,
                    total = total
                };
            }
        }

        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public virtual async Task<PageResponse<TEntityOutput>> GetPageListAsync(TEntitySearch search)
        {
            // 组装查询条件
            Type et = typeof(TEntity);
            //组装条件
            var conModels = GetConditionals(search).Union(GetDefaultCondition(et)).ToList();
            // 判断是否有 pageSize 参数 或者 pageSize = 0，如果没有或者等于0则查询所有
            int pageSize = 0;
            int pageNo = 0;
            if (search.IsHasProperty("page"))
                pageNo = search.GetPropertyValue<TEntitySearch, int>("page");
            if (search.IsHasProperty("limit"))
                pageSize = search.GetPropertyValue<TEntitySearch, int>("limit");
            pageSize = pageSize <= 0 ? 20 : pageSize;
            pageNo = pageNo <= 0 ? 1 : pageNo;

            // 根据主键倒序排列
            var key = GetPrimaryKeys(et)[1];
            if (pageSize == 0)
            {
                var list = await _client.Queryable<TEntity>().OrderBy($"{key} desc").Where(conModels).ToListAsync();
                List<TEntityOutput> dtos = list.Adapt<List<TEntityOutput>>();
                return new PageResponse<TEntityOutput>()
                {
                    rows = dtos,
                    total = list.Count
                };
            }
            else
            {
                RefAsync<int> total = 0;
                var list = await _client.Queryable<TEntity>().OrderBy($"{key} desc").Where(conModels).ToPageListAsync(pageNo, pageSize, total);
                List<TEntityOutput> dtos = list.Adapt<List<TEntityOutput>>();
                return new PageResponse<TEntityOutput>()
                {
                    rows = dtos,
                    total = total.Value
                };
            }
        }

        #endregion 获取分页列表

        #region 根据条件查询指定列返回记录

        /// <summary>
        /// 根据条件查询指定列返回记录
        /// </summary>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="whereExpression"></param>
        /// <param name="selectcolumns"></param>
        /// <param name="orderByExpression"></param>
        /// <param name="orderByType"></param>
        /// <returns></returns>
        public object GetColumnsList<TResult>(Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, TResult>> selectcolumns, Expression<Func<TEntity, object>> orderByExpression = null, OrderByType orderByType = OrderByType.Asc)
        {
            return _client.Queryable<TEntity>().Where(whereExpression).OrderByIF(orderByExpression != null, orderByExpression, orderByType).Select(selectcolumns).ToList();
        }

        /// <summary>
        /// 根据条件查询指定列返回记录
        /// </summary>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="whereExpression"></param>
        /// <param name="selectcolumns"></param>
        /// <param name="orderByExpression"></param>
        /// <param name="orderByType"></param>
        /// <returns></returns>
        public async Task<object> GetColumnsListAsync<TResult>(Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, TResult>> selectcolumns, Expression<Func<TEntity, object>> orderByExpression = null, OrderByType orderByType = OrderByType.Asc)
        {
            return await _client.Queryable<TEntity>().Where(whereExpression).OrderByIF(orderByExpression != null, orderByExpression, orderByType).Select(selectcolumns).ToListAsync();
        }

        #endregion 根据条件查询指定列返回记录

        #region 根据主键查询单条数据

        /// <summary>
        /// 根据主键查询单条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public virtual TEntityOutput GetSingle(TKey id)
        {
            var et = typeof(TEntity);
            List<IConditionalModel> conModels = GetDefaultCondition(et);
            // 设置主键查询
            var key = GetPrimaryKeys(et)[1];
            conModels.Add(new ConditionalModel() { FieldName = key, ConditionalType = ConditionalType.Equal, FieldValue = id.ToString() });
            var result = _client.Queryable<TEntity>().Where(conModels).First();
            return result.Adapt<TEntityOutput>();
        }

        /// <summary>
        /// 根据主键查询单条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public virtual async Task<TEntityOutput> GetSingleAsync(TKey id)
        {
            var et = typeof(TEntity);
            List<IConditionalModel> conModels = GetDefaultCondition(et);
            // 设置主键查询
            var key = GetPrimaryKeys(et)[1];
            conModels.Add(new ConditionalModel() { FieldName = key, ConditionalType = ConditionalType.Equal, FieldValue = id.ToString() });
            var result = await _client.Queryable<TEntity>().Where(conModels).FirstAsync();
            return result.Adapt<TEntityOutput>();
        }

        #endregion 根据主键查询单条数据

        #region 根据表达式查询一条数据

        /// <summary>
        /// 根据表达式查询一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public TEntityOutput GetSingle(Expression<Func<TEntity, bool>> whereExpression)
        {
            var et = typeof(TEntity);
            List<IConditionalModel> conModels = GetDefaultCondition(et);
            var result = _client.Queryable<TEntity>().Where(conModels).Where(whereExpression).Single();
            return result.Adapt<TEntityOutput>();
        }

        /// <summary>
        /// 根据表达式查询一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<TEntityOutput> GetSingleAsync(Expression<Func<TEntity, bool>> whereExpression)
        {
            var et = typeof(TEntity);
            List<IConditionalModel> conModels = GetDefaultCondition(et);
            var result = await _client.Queryable<TEntity>().Where(conModels).Where(whereExpression).SingleAsync();
            return result.Adapt<TEntityOutput>();
        }

        #endregion 根据表达式查询一条数据

        #region 根据主键判断数据是否存在

        /// <summary>
        /// 根据主键判断数据是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool IsAny(TKey id)
        {
            var et = typeof(TEntity);
            // 设置查询条件
            List<IConditionalModel> conModels = GetDefaultCondition(et);
            // 设置主键查询
            var key = GetPrimaryKeys(et)[1];
            conModels.Add(new ConditionalModel() { FieldName = key, ConditionalType = ConditionalType.Equal, FieldValue = id.ToString() });
            return _client.Queryable<TEntity>().Where(conModels).Any();
        }

        /// <summary>
        /// 根据主键判断数据是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<bool> IsAnyAsync(TKey id)
        {
            var et = typeof(TEntity);
            // 设置查询条件
            List<IConditionalModel> conModels = GetDefaultCondition(et);
            // 设置主键查询
            var key = GetPrimaryKeys(et)[1];
            conModels.Add(new ConditionalModel() { FieldName = key, ConditionalType = ConditionalType.Equal, FieldValue = id.ToString() });
            return await _client.Queryable<TEntity>().Where(conModels).AnyAsync();
        }

        #endregion 根据主键判断数据是否存在

        #region 根据表达式判断数据是否存在

        /// <summary>
        /// 根据表达式判断数据是否存在
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public bool IsAny(Expression<Func<TEntity, bool>> whereExpression)
        {
            var et = typeof(TEntity);
            List<IConditionalModel> conModels = GetDefaultCondition(et);
            return _client.Queryable<TEntity>().Where(conModels).Where(whereExpression).Any();
        }

        /// <summary>
        /// 根据表达式判断数据是否存在
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public async Task<bool> IsAnyAsync(Expression<Func<TEntity, bool>> whereExpression)
        {
            var et = typeof(TEntity);
            List<IConditionalModel> conModels = GetDefaultCondition(et);
            return await _client.Queryable<TEntity>().Where(conModels).Where(whereExpression).AnyAsync();
        }

        #endregion 根据表达式判断数据是否存在

        #region 新增一条数据

        /// <summary>
        /// 新增一条数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public virtual TEntityOutput Insert(TEntityInput input)
        {
            string[] keyarray = GetPrimaryKeys(typeof(TEntity));
            var entity = GetEntity(input, keyarray);
            _client.Insertable(entity).ExecuteCommand();
            return entity.Adapt<TEntityOutput>();
        }

        /// <summary>
        /// 新增一条数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public virtual async Task<TEntityOutput> InsertAsync(TEntityInput input)
        {
            string[] keyarray = GetPrimaryKeys(typeof(TEntity));
            var entity = GetEntity(input, keyarray);
            await _client.Insertable(entity).ExecuteCommandAsync();
            return entity.Adapt<TEntityOutput>();
        }

        #endregion 新增一条数据

        #region 更新一条数据

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public virtual TEntityOutput Update(TEntityInput input)
        {
            var entity = GetEntity(input, null, false);
            int result = _client.Updateable(entity).ExecuteCommand();
            if (result <= 0)
                throw new Exception("更新失败");
            return entity.Adapt<TEntityOutput>();
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public virtual async Task<TEntityOutput> UpdateAsync(TEntityInput input)
        {
            var entity = await GetEntityAsync(input, null, false);
            int result = await _client.Updateable(entity).ExecuteCommandAsync();
            if (result <= 0)
                throw new Exception("更新失败");
            return entity.Adapt<TEntityOutput>();
        }

        #endregion 更新一条数据

        #region 根据条件更新指定列

        /// <summary>
        /// 根据条件更新指定列
        /// </summary>
        /// <param name="input"></param>
        /// <param name="updatecolumns"></param>
        /// <param name="updatecolumns"></param>
        /// <returns></returns>
        public TEntityOutput UpdateColumns(TEntityInput input, Expression<Func<TEntity, object>> updatecolumns, Expression<Func<TEntity, bool>> whereExpression = null)
        {
            var entity = GetEntity(input, null, false);
            var obj = _client.Updateable(entity).UpdateColumnsIF(!updatecolumns.IsNullOrEmpty(), updatecolumns).IgnoreColumns();
            if (!whereExpression.IsNullOrEmpty())
            {
                obj.Where(whereExpression);
            }
            int result = obj.ExecuteCommand();
            if (result <= 0)
                throw new Exception("更新失败");
            return entity.Adapt<TEntityOutput>();
        }

        /// <summary>
        /// 根据条件更新指定列
        /// </summary>
        /// <param name="input"></param>
        /// <param name="updatecolumns"></param>
        /// <param name="updatecolumns"></param>
        /// <returns></returns>
        public async Task<TEntityOutput> UpdateColumnsAsync(TEntityInput input, Expression<Func<TEntity, object>> updatecolumns, Expression<Func<TEntity, bool>> whereExpression = null)
        {
            var entity = await GetEntityAsync(input, null, false);
            var obj = _client.Updateable(entity).UpdateColumnsIF(!updatecolumns.IsNullOrEmpty(), updatecolumns).IgnoreColumns();
            if (!whereExpression.IsNullOrEmpty())
            {
                obj.Where(whereExpression);
            }
            int result = await obj.ExecuteCommandAsync();
            if (result <= 0)
                throw new Exception("更新失败");
            return entity.Adapt<TEntityOutput>();
        }

        #endregion 根据条件更新指定列

        #region 删除数据

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="isFake"></param>
        /// <returns></returns>
        public virtual bool Delete(TKey id, bool isFake = true)
        {
            // 实体类型
            var et = typeof(TEntity);
            // 获取实体表名
            var table = GetTableName(et);
            // 获取实体主键
            var key = GetPrimaryKeys(et)[1];
            // 是否伪删除
            if (isFake)
            {
                return _client.Ado.ExecuteCommand($"update {table} set {et.GetColumnName(FieldConst.Isdel)}={IsDel.Yes.GetEnumValue()} where {key}=:ID",
                    new { ID = id }) > 0;
            }
            else
            {
                return _client.Ado.ExecuteCommand($"delete from {table} where {key}=:ID",
                    new { ID = id }) > 0;
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="isFake"></param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(TKey id, bool isFake = true)
        {
            // 实体类型
            var et = typeof(TEntity);
            // 获取实体表名
            var table = GetTableName(et);
            // 获取实体主键
            var key = GetPrimaryKeys(et)[1];
            // 是否伪删除
            if (isFake)
            {
                return await _client.Ado.ExecuteCommandAsync($"update {table} set {et.GetColumnName(FieldConst.Isdel)}={IsDel.Yes.GetEnumValue()} where {key}=:ID",
                    new { ID = id }) > 0;
            }
            else
            {
                return await _client.Ado.ExecuteCommandAsync($"delete from {table} where {key}=:ID",
                    new { ID = id }) > 0;
            }
        }

        #endregion 删除数据

        #region 根据表达式删除数据

        /// <summary>
        /// 根据表达式删除数据
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <param name="isFake">是否逻辑删除</param>
        /// <returns></returns>
        public virtual bool Delete(Expression<Func<TEntity, bool>> whereExpression, bool isFake = true)
        {
            if (!_client.Queryable<TEntity>().Where(whereExpression).Any())
            {
                throw new Exception("数据不存在");
            }
            // 伪删除
            if (isFake)
            {
                var entity = typeof(TEntity);
                var user = AppManager.GetCurrentUser();
                var updatedic = new Dictionary<string, object>();
                // 创建修改人
                if (entity.IsHasProperty(FieldConst.Lastmodifier))
                {
                    updatedic.Add(FieldConst.Lastmodifier, user?.UserCode);
                }
                // 设置修改时间
                if (entity.IsHasProperty(FieldConst.Lastmodified))
                {
                    updatedic.Add(FieldConst.Lastmodified, DateTime.Now);
                }
                // 设置版本号
                if (entity.IsHasProperty(FieldConst.Version))
                {
                    updatedic.Add(FieldConst.Version, FieldConst.Version + "+1");
                }
                // 设置是否删除
                if (entity.IsHasProperty(FieldConst.Isdel))
                {
                    updatedic.Add(FieldConst.Isdel, IsDel.Yes.GetEnumValue());
                }
                return _client.Updateable<TEntity>(updatedic).Where(whereExpression).ExecuteCommand() > 0;
            }
            else
            {
                return _client.Deleteable<TEntity>().Where(whereExpression).ExecuteCommand() > 0;
            }
        }

        /// <summary>
        /// 根据表达式删除数据
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <param name="isFake">是否逻辑删除</param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(Expression<Func<TEntity, bool>> whereExpression, bool isFake = true)
        {
            if (!await _client.Queryable<TEntity>().Where(whereExpression).AnyAsync())
            {
                throw new Exception("数据不存在");
            }
            // 伪删除
            if (isFake)
            {
                var entity = typeof(TEntity);
                var user = AppManager.GetCurrentUser();
                var updatedic = new Dictionary<string, object>();
                // 创建修改人
                if (entity.IsHasProperty(FieldConst.Lastmodifier))
                {
                    updatedic.Add(FieldConst.Lastmodifier, user?.UserCode);
                }
                // 设置修改时间
                if (entity.IsHasProperty(FieldConst.Lastmodified))
                {
                    updatedic.Add(FieldConst.Lastmodified, DateTime.Now);
                }
                // 设置版本号
                if (entity.IsHasProperty(FieldConst.Version))
                {
                    updatedic.Add(FieldConst.Version, FieldConst.Version + "+1");
                }
                // 设置是否删除
                if (entity.IsHasProperty(FieldConst.Isdel))
                {
                    updatedic.Add(FieldConst.Isdel, IsDel.Yes.GetEnumValue());
                }
                return await _client.Updateable<TEntity>(updatedic).Where(whereExpression).ExecuteCommandAsync() > 0;
            }
            else
            {
                return await _client.Deleteable<TEntity>().Where(whereExpression).ExecuteCommandAsync() > 0;
            }
        }

        #endregion 根据表达式删除数据

        #region 批量删除

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="id"></param>
        /// <param name="isFake"></param>
        /// <returns></returns>
        public virtual bool Delete(List<TKey> ids, bool isFake = true)
        {
            // 实体类型
            var et = typeof(TEntity);
            // 获取实体表名
            var table = GetTableName(et);
            // 获取实体主键
            var key = GetPrimaryKeys(et)[1];
            // 是否伪删除
            if (isFake)
            {
                StringBuilder sql = new StringBuilder($"begin {Environment.NewLine}");
                foreach (object item in ids)
                {
                    sql.AppendLine($"update {table} set {et.GetColumnName(FieldConst.Isdel)}={IsDel.Yes.GetEnumValue()} where {key}={StringHelper.ReplaceSQLChar(item?.ToString())};");
                }
                sql.AppendLine("end;");
                return _client.Ado.ExecuteCommand(sql.ToString()) > 0;
            }
            else
            {
                StringBuilder sql = new StringBuilder($"begin {Environment.NewLine}");
                foreach (object item in ids)
                {
                    sql.AppendLine($"delete from {table} where {key}={StringHelper.ReplaceSQLChar(item?.ToString())};");
                }
                sql.AppendLine("end;");
                return _client.Ado.ExecuteCommand(sql.ToString()) > 0;
            }
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="id"></param>
        /// <param name="isFake"></param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(List<TKey> ids, bool isFake = true)
        {
            // 实体类型
            var et = typeof(TEntity);
            // 获取实体表名
            var table = GetTableName(et);
            // 获取实体主键
            var key = GetPrimaryKeys(et)[1];
            // 是否伪删除
            if (isFake)
            {
                StringBuilder sql = new StringBuilder($"begin {Environment.NewLine}");
                foreach (object item in ids)
                {
                    sql.AppendLine($"update {table} set {et.GetColumnName(FieldConst.Isdel)}={IsDel.Yes.GetEnumValue()} where {key}={StringHelper.ReplaceSQLChar(item?.ToString())};");
                }
                sql.AppendLine("end;");
                return await _client.Ado.ExecuteCommandAsync(sql.ToString()) > 0;
            }
            else
            {
                StringBuilder sql = new StringBuilder($"begin {Environment.NewLine}");
                foreach (object item in ids)
                {
                    sql.AppendLine($"delete from {table} where {key}={StringHelper.ReplaceSQLChar(item?.ToString())};");
                }
                sql.AppendLine("end;");
                return await _client.Ado.ExecuteCommandAsync(sql.ToString()) > 0;
            }
        }

        #endregion 批量删除

        #region 获取实体

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="input"></param>
        /// <param name="keyarray"></param>
        /// <param name="isadd"></param>
        /// <returns></returns>
        public TEntity GetEntity(TEntityInput input, string[] keyarray = null, bool isadd = true)
        {
            var entity = input.Adapt<TEntity>();
            // 设置主键属性
            keyarray = keyarray ?? GetPrimaryKeys(entity.GetType());
            var user = AppManager.GetCurrentUser();
            // 设置修改人
            if (entity.IsHasProperty(FieldConst.Lastmodifier))
            {
                entity.SetPropertyValue(FieldConst.Lastmodifier, user?.UserCode);
            }
            // 设置修改时间
            if (entity.IsHasProperty(FieldConst.Lastmodified))
            {
                entity.SetPropertyValue(FieldConst.Lastmodified, DateTime.Now);
            }

            if (isadd)
            {
                //设置主键雪花ID
                if (!keyarray[0].IsNullOrEmpty())
                {
                    if (keyarray[0] == "Id")
                    {
                        entity.SetPropertyValue(keyarray[0], Ids.NextIdString());
                    }
                }
                // 设置企业Id --> 默认字段名 FltId
                // input模型中没有设置企业id，则设置当前登录人的企业id
                // 判断是否企业表的主键
                if (entity.IsHasProperty(FieldConst.FltId))
                {
                    entity.SetPropertyValue(FieldConst.FltId, user?.UsrFltId);
                }
                // 设置创建人 -->  默认字段名 UserId
                if (entity.IsHasProperty(FieldConst.Creator))
                {
                    entity.SetPropertyValue(FieldConst.Creator, user?.UserCode);
                }
                // 设置创建时间
                if (entity.IsHasProperty(FieldConst.Created))
                {
                    entity.SetPropertyValue(FieldConst.Created, DateTime.Now);
                }
                // 设置版本号
                if (entity.IsHasProperty(FieldConst.Version))
                {
                    entity.SetPropertyValue(FieldConst.Version, 1);
                }
                // 设置是否删除
                if (entity.IsHasProperty(FieldConst.Isdel))
                {
                    entity.SetPropertyValue(FieldConst.Isdel, IsDel.No.GetEnumValue());
                }
            }
            else
            {
                // 获取传入参数中主键字段值
                var value = entity.GetPropertyValue<TEntity, string>(keyarray[0]);
                // 获取数据库中的数据
                var snap = _client.Queryable<TEntity>().Where(new List<IConditionalModel>()
            {
                new ConditionalModel(){ FieldName =  keyarray[1], ConditionalType = ConditionalType.Equal, FieldValue = value.ToString() }
            }).First();
                if (snap == null)
                {
                    throw new Exception("数据不存在");
                }
                // 设置版本号
                if (entity.IsHasProperty(FieldConst.Version))
                {
                    entity.SetPropertyValue(FieldConst.Version, snap.GetPropertyValue<TEntity, int>(FieldConst.Version) + 1);
                }
                // 设置是否删除
                if (entity.IsHasProperty(FieldConst.Isdel))
                {
                    entity.SetPropertyValue(FieldConst.Isdel, snap.GetPropertyValue<TEntity, string>(FieldConst.Isdel));
                }
            }
            return entity;
        }

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="input"></param>
        /// <param name="keyarray"></param>
        /// <param name="isadd"></param>
        /// <returns></returns>
        public async Task<TEntity> GetEntityAsync(TEntityInput input, string[] keyarray = null, bool isadd = true)
        {
            var entity = input.Adapt<TEntity>();
            // 设置主键属性
            keyarray = keyarray ?? GetPrimaryKeys(entity.GetType());
            var user = AppManager.GetCurrentUser();
            // 设置修改人
            if (entity.IsHasProperty(FieldConst.Lastmodifier))
            {
                entity.SetPropertyValue(FieldConst.Lastmodifier, user?.UserCode);
            }
            // 设置修改时间
            if (entity.IsHasProperty(FieldConst.Lastmodified))
            {
                entity.SetPropertyValue(FieldConst.Lastmodified, DateTime.Now);
            }

            if (isadd)
            {
                //设置主键雪花ID
                if (!keyarray[0].IsNullOrEmpty())
                {
                    if (keyarray[0] == "Id")
                    {
                        entity.SetPropertyValue(keyarray[0], Ids.NextIdString());
                    }
                }
                // 设置企业Id --> 默认字段名 FltId
                // input模型中没有设置企业id，则设置当前登录人的企业id
                // 判断是否企业表的主键
                if (entity.IsHasProperty(FieldConst.FltId))
                {
                    entity.SetPropertyValue(FieldConst.FltId, user?.UsrFltId);
                }
                // 设置创建人 -->  默认字段名 UserId
                if (entity.IsHasProperty(FieldConst.Creator))
                {
                    entity.SetPropertyValue(FieldConst.Creator, user?.UserCode);
                }
                // 设置创建时间
                if (entity.IsHasProperty(FieldConst.Created))
                {
                    entity.SetPropertyValue(FieldConst.Created, DateTime.Now);
                }
                // 设置版本号
                if (entity.IsHasProperty(FieldConst.Version))
                {
                    entity.SetPropertyValue(FieldConst.Version, 1);
                }
                // 设置是否删除
                if (entity.IsHasProperty(FieldConst.Isdel))
                {
                    entity.SetPropertyValue(FieldConst.Isdel, IsDel.No.GetEnumValue());
                }
            }
            else
            {
                // 获取传入参数中主键字段值
                var value = entity.GetPropertyValue<TEntity, string>(keyarray[0]);
                // 获取数据库中的数据
                var snap = await _client.Queryable<TEntity>().Where(new List<IConditionalModel>()
            {
                new ConditionalModel(){ FieldName =  keyarray[1], ConditionalType = ConditionalType.Equal, FieldValue = value.ToString() }
            }).FirstAsync();
                if (snap == null)
                {
                    throw new Exception("数据不存在");
                }
                // 设置版本号
                if (entity.IsHasProperty(FieldConst.Version))
                {
                    entity.SetPropertyValue(FieldConst.Version, snap.GetPropertyValue<TEntity, int>(FieldConst.Version) + 1);
                }
                // 设置是否删除
                if (entity.IsHasProperty(FieldConst.Isdel))
                {
                    entity.SetPropertyValue(FieldConst.Isdel, snap.GetPropertyValue<TEntity, string>(FieldConst.Isdel));
                }
            }
            return entity;
        }

        #endregion 获取实体

        #region 执行主存储过程
        /// <summary>
        /// 汤文
        /// 2022/03/20
        /// </summary>
        /// <param name="actionName">动作名</param>
        /// <param name="para">键</param>
        /// <param name="values">值</param>
        /// <param name="returnValue">返回值</param>
        /// <returns></returns>
        public void TransferStoredProc(string actionName, string para, string values, out string returnValue)
        {
            var i_file = new SqlSugar.SugarParameter(":I_FILE", actionName);
            var i_parm = new SqlSugar.SugarParameter(":I_PARM", para);
            var i_value = new SqlSugar.SugarParameter(":I_VALUE", values);
            var o_value = new SqlSugar.SugarParameter(":O_VALUE", null, true);//isOutput=true
            _client.Ado.UseStoredProcedure().GetString("SAVE_VALUES", i_file, i_parm, i_value, o_value);
            returnValue = o_value.Value?.ToString();
        }
        #endregion

        /// <summary>
        /// 获取默认查询条件
        /// </summary>
        /// <param name="et"></param>
        /// <returns></returns>
        public List<IConditionalModel> GetDefaultCondition(Type et, string prefix = "")
        {
            // 设置查询条件
            List<IConditionalModel> conModels = new List<IConditionalModel>();
            // 设置是否删除
            if (et.IsHasProperty(FieldConst.Isdel))
            {
                conModels.Add(new ConditionalModel() { FieldName = (prefix.IsNullOrEmptyOrWhiteSpace() ? "" : prefix + ".") + et.GetColumnName(FieldConst.Isdel), ConditionalType = ConditionalType.Equal, FieldValue = IsDel.No.GetEnumValue() });
            }
            var user = AppManager.GetCurrentUser();
            if (user != null)
            {
                // 设置企业id字段，如果FltId=-1，则不加此条件
                var fltid = user.UsrFltId;
                if (et.IsHasProperty(FieldConst.FltId) && fltid != SysCompanyConst.SuperAdminFltId)
                {
                    conModels.Add(new ConditionalModel() { FieldName = (prefix.IsNullOrEmptyOrWhiteSpace() ? "" : prefix + ".") + et.GetColumnName(FieldConst.FltId), ConditionalType = ConditionalType.Equal, FieldValue = fltid.ToString() });
                }
            }
            return conModels;
        }

        /// <summary>
        /// 获取主键信息
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public virtual string[] GetPrimaryKeys(Type t)
        {
            var entityInfo = _client.EntityMaintenance.GetEntityInfo(t);
            var primarycolumn = entityInfo.Columns.Where(it => it.IsPrimarykey).FirstOrDefault();
            return new string[] { primarycolumn.PropertyName, primarycolumn.DbColumnName, primarycolumn.OracleSequenceName };
        }



        /// <summary>
        /// 获取实体表名
        /// </summary>
        /// <param name="et"></param>
        /// <returns></returns>
        public static string GetTableName(Type et = null)
        {
            if (et == null)
                et = typeof(TEntity);
            var table = et.GetTableName();
            if (table.IsNullOrEmptyOrWhiteSpace())
                throw new Exception("获取表名失败");
            return table;
        }

        /// <summary>
        /// 获取数据库字段名
        /// </summary>
        /// <param name="type"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public static string GetColumnName(Type type, string name)
        {
            var property = type.GetProperty(name);
            var sugarAttribute = (SugarColumn)property.GetCustomAttributes(true)
                .First(it => it is SugarColumn);
            return sugarAttribute?.ColumnName;
        }

        /// <summary>
        /// 根据查询模型获取条件
        /// </summary>
        /// <param name="search"></param>
        /// <param name="prefix"></param>
        /// <returns></returns>
        public virtual List<IConditionalModel> GetConditionals(TEntitySearch search, string prefix = "")
        {
            List<IConditionalModel> conModels = new List<IConditionalModel>();
            if (search.IsNullOrEmpty())
            {
                return conModels;
            }
            Type type = search.GetType();
            var propertys = type.GetProperties()
                .Where(it => it.GetCustomAttributes(typeof(SearchAttribute), true).Any())
                .ToList();
            Type et = typeof(TEntity);
            if (propertys != null)
            {
                foreach (var p in propertys)
                {
                    var v = p.GetValue(search, null)?.ToString();
                    if (v.IsNullOrEmptyOrWhiteSpace()) continue;

                    var s = (SearchAttribute)p.GetCustomAttributes(true).First(it => it is SearchAttribute);
                    if (!s.IsNullOrEmpty())
                    {
                        conModels.Add(new ConditionalModel
                        {
                            FieldName = (prefix.IsNullOrEmptyOrWhiteSpace() ? "" : prefix + ".") + (s.FieldName.IsNullOrEmptyOrWhiteSpace() ? et.GetColumnName(p.Name) : s.FieldName),
                            ConditionalType = s.ConditionalType,
                            FieldValue = v
                        });
                    }
                }
            }
            return conModels;
        }
    }
}